package com.dicsystems.myrec.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.dicsystems.myrec.Common;
import com.dicsystems.myrec.DatabaseHelper;
import com.dicsystems.myrec.dto.Todo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class TodoDao {

	private Context me;
	private Common co = new Common();
	private SQLiteDatabase db;
	private final String TABLE = "todo";
	private final int ID = 0;
	private final int START_DATE = 1;
	private final int START_TIME = 2;
	private final int END_DATE = 3;
	private final int END_TIME = 4;
	private final int TITLE = 5;
	private final int CATEGORY_ID = 6;
	private final int MEMO = 7;
	private final int LATITUDE = 8;
	private final int LONGITUDE = 9;
	private final int PLACE = 10;
	private final int CATEGORY_NAME = 11;

	public TodoDao(Context context) {
		this.me = context;
		co.me = context;
	}

	public void open() {
		db = new DatabaseHelper(me).getReadableDatabase();
	}

	public void close() {
		db.close();
	}

	public void insert(Todo todo) {
		db.insert(TABLE, null, todo.get());
	}

	public void update(Todo todo) {
		db.update(TABLE, todo.get(), "_id = ?", new String[] {todo.id});
	}

	public void delete(Todo todo) {
		db.delete(TABLE, "_id = ?", new String[] {todo.id});
	}

	public List<Todo> select(String sql, String[] bind) {
		List<Todo> todos = new ArrayList<Todo>();
		Cursor cursor = db.rawQuery(sql, bind);
		if(cursor.moveToFirst()) {
			do {
				Todo todo = new Todo();
				todo.id = cursor.getString(ID);
				todo.start_date = cursor.getString(START_DATE);
				todo.start_time = cursor.getString(START_TIME);
				todo.end_date = cursor.getString(END_DATE);
				todo.end_time = cursor.getString(END_TIME);
				todo.title = cursor.getString(TITLE);
				todo.category_id = cursor.getString(CATEGORY_ID);
				todo.memo = cursor.getString(MEMO);
				todo.latitude = cursor.getString(LATITUDE);
				todo.longitude = cursor.getString(LONGITUDE);
				todo.place = cursor.getString(PLACE);
				todo.category_name = cursor.getString(CATEGORY_NAME);
				todos.add(todo);
			} while(cursor.moveToNext());
		}
		cursor.close();
		return todos;
	}

	public List<Todo> select() {
		return select(co.read("sql/select_todo.sql"), null);
	}

	public List<Todo> select(Calendar date) {
		return select(co.read("sql/select_todo_by_date.sql"), new String[] {co.date(date), co.date(date)});
	}

	public List<Todo> select(Calendar start_date, Calendar end_date) {
		return select(co.read("sql/select_todo_by_date.sql"), new String[] {co.date(start_date), co.date(end_date)});
	}

	public String id() {
		int id = 0;
		Cursor cursor = db.query(TABLE, new String[] {"MAX(_id) + 1"}, null, null, null, null, null);
		if(cursor.moveToFirst()) {
			id = cursor.getInt(0);
		}
		cursor.close();
		return Integer.toString(id);
	}
}
